package dao;

import beans.Assignment;
import helper.ReadConfig;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MngAssignment {

    public boolean createAssign(Assignment assign) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "insert into tbl_Assignment(Acc_Id, Class_Id,"
                    + "  Ass_Name, Creat_Date, Finish_Date) "
                    + " values(" + assign.getAcc_id() + ","
                    + assign.getClass_id() + ",'" + assign.getAssign_name() + "','"
                    + assign.getCreate_date() + "','" + assign.getFinish_date() + "')";
            System.out.println(sSQL);
            Statement stm = con.createStatement();
            int rs = stm.executeUpdate(sSQL);
            System.out.println(rs);
            if (rs == 1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean updateAssign(Assignment assign) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            System.out.println(Integer.parseInt(assign.getAssign_id().toString()));
            String sSQL = "Update tbl_Assignment set "
                    + " Ass_Name = '" + assign.getAssign_name()
                    + "', Creat_Date = '" + assign.getCreate_date()
                    + "', Finish_Date = '" + assign.getFinish_date()
                    + "' WHERE Ass_Id = " + Integer.parseInt(assign.getAssign_id());
            System.out.println(sSQL);
            Statement stm = con.createStatement();
            int rs = stm.executeUpdate(sSQL);
            System.out.println(rs);
            if (rs == 1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean deleteAssign(String assign_id) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "DELETE FROM tbl_Assignment WHERE Ass_Id = " + Integer.parseInt(assign_id);
            Statement stm = con.createStatement();
            int rs = stm.executeUpdate(sSQL);
            if (rs == 1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public List<Assignment> showAssign() {
        try {
            List<Assignment> listAssign = new ArrayList<Assignment>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Assignment ";
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            while (rs.next()) {
                Assignment assign = new Assignment();
                assign.setAssign_id(rs.getString("Ass_Id"));
                assign.setAcc_id(rs.getString("Acc_Id"));
                assign.setClass_id(rs.getString("Class_Id"));
                assign.setAssign_name(rs.getString("Ass_Name"));
                assign.setCreate_date(rs.getString("Creat_Date"));
                assign.setFinish_date(rs.getString("Finish_Date"));
                assign.setMark(rs.getString("Mark"));
                assign.setFile_ass(rs.getString("File_Ass"));
                assign.setFile_finishass(rs.getString("File_FinishAss"));
                listAssign.add(assign);
            }
            rs.close();
            stm.close();
            return listAssign;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<Assignment> showAssignByAccount(String acc_id) {
        try {
            List<Assignment> listAssign = new ArrayList<Assignment>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Assignment where Acc_Id=" + Integer.parseInt(acc_id);
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            while (rs.next()) {
                Assignment assign = new Assignment();
                assign.setAssign_id(rs.getString("Ass_Id"));
                assign.setAcc_id(rs.getString("Acc_Id"));
                assign.setClass_id(rs.getString("Class_Id"));
                assign.setAssign_name(rs.getString("Ass_Name"));
                assign.setCreate_date(rs.getString("Creat_Date"));
                assign.setFinish_date(rs.getString("Finish_Date"));
                assign.setMark(rs.getString("Mark"));
                assign.setFile_ass(rs.getString("File_Ass"));
                assign.setFile_finishass(rs.getString("File_FinishAss"));
                listAssign.add(assign);
            }
            rs.close();
            stm.close();
            return listAssign;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<Assignment> showAssignByAccountAndClass(String acc_id, String class_id) {
        try {
            List<Assignment> listAssign = new ArrayList<Assignment>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Assignment where Acc_Id=" + Integer.parseInt(acc_id)
                    + " and Class_Id=" + Integer.parseInt(class_id);
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            while (rs.next()) {
                Assignment assign = new Assignment();
                assign.setAssign_id(rs.getString("Ass_Id"));
                assign.setAcc_id(rs.getString("Acc_Id"));
                assign.setClass_id(rs.getString("Class_Id"));
                assign.setAssign_name(rs.getString("Ass_Name"));
                assign.setCreate_date(rs.getString("Creat_Date"));
                assign.setFinish_date(rs.getString("Finish_Date"));
                assign.setMark(rs.getString("Mark"));
                assign.setFile_ass(rs.getString("File_Ass"));
                assign.setFile_finishass(rs.getString("File_FinishAss"));
                listAssign.add(assign);
            }
            rs.close();
            stm.close();
            return listAssign;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean setMark(Assignment assign) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            System.out.println(Integer.parseInt(assign.getAssign_id().toString()));
            String sSQL = "Update tbl_Assignment set "
                    + "mark = " + Float.parseFloat(assign.getMark())
                    + " WHERE Ass_Id = " + Integer.parseInt(assign.getAssign_id());

            System.out.println(sSQL);
            Statement stm = con.createStatement();
            int rs = stm.executeUpdate(sSQL);
            System.out.println(rs);
            if (rs == 1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean updateFileFinishAss(String assign_id, String file_finishass) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "Update tbl_Assignment set "
                    + " File_FinishAss = '" + file_finishass
                    + "' WHERE Ass_Id = " + Integer.parseInt(assign_id);
            System.out.println(sSQL);
            Statement stm = con.createStatement();
            int rs = stm.executeUpdate(sSQL);
            System.out.println(rs);
            if (rs == 1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean updateFileAss(String assign_id, String file_ass) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "Update tbl_Assignment set "
                    + " File_Ass = '" + file_ass
                    + "' WHERE Ass_Id = " + Integer.parseInt(assign_id);
            System.out.println(sSQL);
            Statement stm = con.createStatement();
            int rs = stm.executeUpdate(sSQL);
            System.out.println(rs);
            if (rs == 1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public List<Assignment> showAssignByClass(String class_id) {
        try {
            List<Assignment> listAssign = new ArrayList<Assignment>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Assignment where Class_Id=" + class_id;
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            while (rs.next()) {
                Assignment assign = new Assignment();
                assign.setAssign_id(rs.getString("Ass_Id"));
                assign.setAcc_id(rs.getString("Acc_Id"));
                assign.setClass_id(rs.getString("Class_Id"));
                assign.setAssign_name(rs.getString("Ass_Name"));
                assign.setCreate_date(rs.getString("Creat_Date"));
                assign.setFinish_date(rs.getString("Finish_Date"));
                assign.setMark(rs.getString("Mark"));
                assign.setFile_ass(rs.getString("File_Ass"));
                assign.setFile_finishass(rs.getString("File_FinishAss"));
                listAssign.add(assign);
            }
            rs.close();
            stm.close();
            return listAssign;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean checkAssignName(String assign_name) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Assignment where Ass_Name = '" + assign_name + "'";
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            if (rs.next()) {
                return true;
            }
            rs.close();
            stm.close();
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public List<String> listAssignNameByClass(String class_id) {
        try {
            List<String> listAssign = new ArrayList<String>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Assignment where Class_Id=" + class_id;
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            String str_name = "";
            while (rs.next()) {
                if (!str_name.equals(rs.getString("Ass_Name"))) {
                    listAssign.add(rs.getString("Ass_Name"));
                }
                str_name = rs.getString("Ass_Name");
            }
            rs.close();
            stm.close();
            return listAssign;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<Assignment> showAssignByClassAndName(String class_id, String assign_name) {
        try {
            List<Assignment> listAssign = new ArrayList<Assignment>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Assignment where Class_Id=" + class_id
                    + " and  Ass_Name = '" + assign_name + "'";
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            while (rs.next()) {
                Assignment assign = new Assignment();
                assign.setAssign_id(rs.getString("Ass_Id"));
                assign.setAcc_id(rs.getString("Acc_Id"));
                assign.setClass_id(rs.getString("Class_Id"));
                assign.setAssign_name(rs.getString("Ass_Name"));
                assign.setCreate_date(rs.getString("Creat_Date"));
                assign.setFinish_date(rs.getString("Finish_Date"));
                assign.setMark(rs.getString("Mark"));
                assign.setFile_ass(rs.getString("File_Ass"));
                assign.setFile_finishass(rs.getString("File_FinishAss"));
                listAssign.add(assign);
            }
            rs.close();
            stm.close();
            return listAssign;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<Assignment> listAssignByClass(String class_id) {
        try {
            List<Assignment> listAssign = new ArrayList<Assignment>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Assignment where Class_Id=" + class_id;
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            String str_name = "";
            List<String> listStr = new ArrayList<String>();
            listStr.add(str_name);            
            while (rs.next()) {
                int i = 0;
                for (String str : listStr) {
                    if (!str.equals(rs.getString("Ass_Name"))) {                        
                        i++;
                    }
                }
                if (i == listStr.size()) { 
                    Assignment assign = new Assignment();
                    assign.setAssign_name(rs.getString("Ass_Name"));
                    assign.setCreate_date(rs.getString("Creat_Date"));
                    assign.setFinish_date(rs.getString("Finish_Date"));
                    assign.setAssign_name(rs.getString("Ass_Name"));
                    assign.setFile_ass(rs.getString("File_Ass"));
                    assign.setClass_id(class_id);
                    listAssign.add(assign);
                    listStr.add(rs.getString("Ass_Name"));
                }
            }
            rs.close();
            stm.close();
            return listAssign;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean addAssignForNewStudent(Assignment assign) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "";
            if (assign.getFile_ass() == null || assign.getFile_ass().equals("")) {
                sSQL = "insert into tbl_Assignment(Acc_Id, Class_Id,"
                        + "  Ass_Name, Creat_Date, Finish_Date) "
                        + " values(" + assign.getAcc_id() + ","
                        + assign.getClass_id() + ",'" + assign.getAssign_name() + "','"
                        + assign.getCreate_date() + "','" + assign.getFinish_date()
                        + "')";
            } else {
                sSQL = "insert into tbl_Assignment(Acc_Id, Class_Id,"
                        + "  Ass_Name, Creat_Date, Finish_Date, File_Ass) "
                        + " values(" + assign.getAcc_id() + ","
                        + assign.getClass_id() + ",'" + assign.getAssign_name() + "','"
                        + assign.getCreate_date() + "','" + assign.getFinish_date() + "','"
                        + assign.getFile_ass() + "')";
            }
            System.out.println(sSQL);
            Statement stm = con.createStatement();
            int rs = stm.executeUpdate(sSQL);
            System.out.println(rs);
            if (rs == 1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

}
